import datetime
from typing import List, Dict

from sqlalchemy.orm import Session
from sqlalchemy import Column, Date, Integer, String, Index, TEXT, FLOAT
from sqlalchemy import and_, func, or_

from common.database import Base
from common.database import SessionMixin

from common.utils import get_month_range


class QosHttpRequest(Base, SessionMixin):
    __tablename__ = 'qos_http_request'
    log_date = Column(Date, nullable=False, comment="请求日期")
    domain_name = Column(String(128), nullable=False, comment="域名")
    verb = Column(String(128), nullable=False, comment="请求方法")
    url = Column(TEXT, nullable=False, comment="请求路由")
    avg_req_time = Column(FLOAT, nullable=False, comment='请求时间')
    request_count = Column(Integer, nullable=False, comment="请求数量")
    http_status_1xx = Column(Integer, nullable=False, comment="1xx状态码数量")
    http_status_2xx = Column(Integer, nullable=False, comment="2xx状态码数量")
    http_status_3xx = Column(Integer, nullable=False, comment="3xx状态码数量")
    http_status_4xx = Column(Integer, nullable=False, comment="4xx状态码数量")
    http_status_5xx = Column(Integer, nullable=False, comment="5xx状态码数量")

    __table_args__ = (
        Index('idx_qos_http_request_uuid', 'uuid', unique=True),
        Index('idx_qos_http_request_log_date', 'log_date'),
        {
            'mysql_engine': 'innodb',
            'mysql_charset': 'utf8mb4',
            'mysql_collate': 'utf8mb4_unicode_ci',
            'extend_existing': True,
            'mysql_row_format': 'dynamic',
        }
    )

    @classmethod
    def get_req_time_avg_aggregate(cls, db: Session, data_month):
        first_day, last_day = get_month_range(data_month)
        return db.query(cls).with_entities(cls.domain_name, cls.verb, cls.url,
                                           func.sum(cls.request_count).label('request_count'),
                                           func.round(func.avg(cls.avg_req_time), 2).label('avg_req_time')).filter(
            cls.log_date.between(first_day, last_day)).group_by(cls.domain_name, cls.verb, cls.url).having(
            func.sum(cls.request_count) > 100).to_dict()

    @classmethod
    def get_http_status_count_aggregate(cls, db: Session, data_month):
        first_day, last_day = get_month_range(data_month)
        return db.query(cls).with_entities(cls.domain_name, cls.verb, cls.url,
                                           func.sum(cls.request_count).label('request_count'),
                                           func.sum(
                                               cls.http_status_1xx + cls.http_status_2xx + cls.http_status_3xx).label(
                                               'http_status_2xx'),
                                           func.sum(cls.http_status_4xx).label('http_status_4xx'),
                                           func.sum(cls.http_status_5xx).label('http_status_5xx')).filter(
            cls.log_date.between(first_day, last_day)).group_by(cls.domain_name, cls.verb, cls.url).having(
            func.sum(cls.request_count) > 100).to_dict()
